//
//  SYStatusDAL.swift
//  SingWeibo
//
//  Created by shiying on 16/1/4.
//  Copyright © 2016年 shiying. All rights reserved.
//

import UIKit

class SYStatusDAL: NSObject {

    
    //加载数据： 1.从本地加载 2.从网络加载
    private let overTime: NSTimeInterval = 60 //一般都是7天
    
    static let sharedInstance = SYStatusDAL() // 单例
    
    private override init() {
        super.init()
    }
    
    /// 清除数据库中过时的微博数据
    func clearCachedStatus() {
        // 1. 计算过期时间
        let overDate = NSDate(timeIntervalSinceNow: -overTime)
        // 定义NSDateFormatter
        let df = NSDateFormatter()
        
        // 设置日期转换的格式
        df.dateFormat = "yyyy-MM-dd HH:mm:ss"
        
        let dateString = df.stringFromDate(overDate)
        // 编写sql语句来清除数据库中的时期数据
        let sql = "DELETE FROM T_Status WHERE createTime < '\(dateString)';"
        
        SYSQLiteManager.sharedManager.dbQueue.inDatabase { (db) -> Void in
            if db.executeUpdate(sql) {
                print("清除数据成功")
            } else {
                print("清除数据失败")
            }
        }
    }
    
    // 加载微博数据 
    func loadStatus(since_id: Int, max_id: Int, finished: (statuses: [[String: AnyObject]]?, error: NSError?) ->()) {
        // 1.从本地加载数据
        loadCacheStatus(since_id, max_id: max_id) { (statuses) -> () in
            if statuses != nil && statuses!.count > 0 {
                print("---从本地数据库获取到了数据")
                finished(statuses: statuses, error: nil)
                return
            }
            
            print("*****本地没有数据，去网络加载数据")
            SYAFNTool.sharedInsetace.loadStatus(since_id, max_id: max_id, finished: { (result, error) -> () in
                if error != nil {
                    finished(statuses: nil, error: error)
                    return
                }
                // 网络加载数据后保存到数据库
                // 判断字典是否在statuses ，将statuses 的值 转成数组,数组里面存放的是字典
                if let array = result?["statuses"] as? [[String: AnyObject]] {
                    self.saveStatus(array)
                    finished(statuses: array, error: nil)
                } else { // 告诉调用者没有解析到需要的数据
                    finished(statuses: nil, error: nil)
                }
            })
        }
    }
    
    // 加载本地数据
    private func loadCacheStatus(since_id: Int, max_id: Int, finished: (statuses: [[String: AnyObject]]?) ->()) {
        // 获取用户的id
        let userId = SYUseAccount.loadUserAccount()?.uid
        
        assert(userId != nil, "用户id为空")
        
        // 编写sql语句
        var sql = "SELECT statusId, status, userId FROM T_Status \n" +
        "WHERE userId = \(userId!) \n"
        
        // 下拉刷新
        if since_id > 0 {
            sql += "AND statusId > \(since_id) \n"
        } else if max_id > 0{ // 上拉加载更多
            sql += "AND statusId < \(max_id) \n"
        }
    
        // 拼接根据id排序,并去最前面20条
        sql += "ORDER BY statusId DESC LIMIT 20;"
        print("查询微博数据的sql:\(sql)")
        
        SYSQLiteManager.sharedManager.dbQueue.inDatabase { (db) -> Void in
            if let recordSet = db.executeQuery(sql) {
                // 获取数据
                var statusesArray = [[String: AnyObject]]()
                while recordSet.next() {
                    // 微博数据的字符串
                    let string = recordSet.stringForColumn("status")
                    
                    // 字符串转字典 
                    let data = string.dataUsingEncoding(NSUTF8StringEncoding)!
                    
                    let dict = try! NSJSONSerialization.JSONObjectWithData(data, options: NSJSONReadingOptions(rawValue: 0)) as! [String: AnyObject]
                    statusesArray.append(dict)
                }
                // 获取到了需要的数据
                finished(statuses: statusesArray)
            } else {
                print("没有获得数据")
                finished(statuses: nil)
                return
            }
        }
    }
    
    // 将微博数据保存到数据库
    private func saveStatus(statuses: [[String: AnyObject]]) {
        let sql = "INSERT OR REPLACE INTO \(T_Status) (statusId, status, userId) VALUES (?, ?, ?);"
        print("添加微博数据的sql \(sql)")
        
        // 获取用户的id
        let userId = SYUseAccount.loadUserAccount()?.uid
        assert(userId != nil, "用户id为空")
        
        SYSQLiteManager.sharedManager.dbQueue.inTransaction { (db, rollback) -> Void in
            // 遍历数组，获取里面的字典，保存到数据库
            for dict in statuses {
                // 获取微博的id
                let id = dict["id"] as! Int
                
                let data = try! NSJSONSerialization.dataWithJSONObject(dict, options: NSJSONWritingOptions.PrettyPrinted)
                let string = String(data: data, encoding: NSUTF8StringEncoding)!
                
                // 如果添加的数据有失败的,整个数据都失败
                if !db.executeUpdate(sql, id, string, userId!) {
                    print("添加数据出错了，回滚事务")
                    rollback.memory = true
                    break
                }
            }
            print("添加\(statuses.count)条数据到数据库")
        }
    
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
}
